﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
      public class InsRow
      {
        public string InventoryCarriageNumber { get; set; }
        public string WeightTons { get; set; }
        public string DestinationStation { get; set; }
        public string CargoCodeTariff { get; set; }
        public string СonsigneeCode { get; set; }
        public string ParkType { get; set; }
        public string StartStation { get; set; }
        public string OperationCode { get; set; }
        public string OperationDate { get; set; }
        public string OperationTime { get; set; }
        public string StationOfOperation { get; set; }
        public string DeliveryRoad { get; set; }
        public string ReceptionRoad { get; set; }
        public string TrainIndex { get; set; }
        public string TrainNumber { get; set; }
      }


      public class CargoRows
      {
          public int ID { get; set; }
          public string CargoName { get; set; }
          public string CargoDescription { get; set; }
          public string CargoCode { get; set; }
          public string Mnemocode { get; set; }
          public bool IsUsed { get; set; }
          public bool IsEmpty { get; set; }
          public object Image { get; set; }
      }


      public class CargoObjDataSource
      {

          private static readonly string ConnectionStringKey = "SUPSDB2";
          private static string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
          SqlConnection con = new SqlConnection(connectionString);
         
          
          public void Update(CargoRows row)
          {
              SqlCommand command = new SqlCommand("CargoUpdate", con);
              command.CommandType = CommandType.StoredProcedure;
              if (row.CargoName == null) row.CargoName = "<Не определено>";
              if (row.CargoDescription == null) row.CargoDescription = "<Не определено>";
              if (row.Mnemocode == null) row.Mnemocode = "<Не определено>";
              if (row.CargoCode == null) row.CargoCode = "<Не определено>";

              command.Parameters.AddWithValue("@CargoName", row.CargoName);
              command.Parameters.AddWithValue("@CargoDescription", row.CargoDescription);
              command.Parameters.AddWithValue("@Mnemocode", row.Mnemocode);
              command.Parameters.AddWithValue("@IsEmpty", row.IsEmpty);
              command.Parameters.AddWithValue("@IsUsed", row.IsUsed);
              command.Parameters.AddWithValue("@ID", row.ID);
              command.Parameters.AddWithValue("@CargoCode", row.CargoCode);


              con.Open();

              command.ExecuteNonQuery();
              con.Close();

          }


          public void UpdatiPic(string CargoName, string CargoDescription, string CargoCode, string Mnemocode, bool IsUsed, bool IsEmpty, int ID, Object Image)
          {
              SqlCommand command = new SqlCommand("UpdateWithPic", con);
              command.CommandType = CommandType.StoredProcedure;
              command.Parameters.AddWithValue("@CargoName", CargoName);
              command.Parameters.AddWithValue("@CargoDescription", CargoDescription);
              command.Parameters.AddWithValue("@Mnemocode", Mnemocode);
              command.Parameters.AddWithValue("@IsEmpty", IsEmpty);
              command.Parameters.AddWithValue("@IsUsed", IsUsed);
              command.Parameters.AddWithValue("@ID", ID);
              command.Parameters.AddWithValue("@CargoCode", CargoCode);
              command.Parameters.AddWithValue("@Image", Image);

              con.Open();

              command.ExecuteNonQuery();
              con.Close();
          }


          public void Delete(int ID)
          {
              SqlCommand command = new SqlCommand("CargoDelete", con);
              con.Open();
              command.CommandType = CommandType.StoredProcedure;
              command.Parameters.AddWithValue("@ID", ID);
              command.ExecuteNonQuery();
              con.Close();
          }


          public void InsertPic(string CargoName, string CargoDescription, string CargoCode, string Mnemocode, bool IsUsed, bool IsEmpty, Object Image)
          {
              SqlCommand command = new SqlCommand("CargoInsertWithPic", con);
              command.Connection = con;
              command.CommandType = CommandType.StoredProcedure;
              

              command.Parameters.AddWithValue("@CargoName", CargoName);
              command.Parameters.AddWithValue("@CargoDescription", CargoDescription);
              command.Parameters.AddWithValue("@Mnemocode", Mnemocode);
              command.Parameters.AddWithValue("@IsEmpty", IsEmpty);
              command.Parameters.AddWithValue("@IsUsed", IsUsed);
              command.Parameters.AddWithValue("@CargoCode", CargoCode);
              if (CargoCode == null) command.Parameters.AddWithValue("@ShortCode", 0);
              else
                  command.Parameters.AddWithValue("@ShortCode", CargoCode.Substring(0, CargoCode.Length - 1));
              command.Parameters.AddWithValue("@Image", Image);


              con.Open();

              command.ExecuteNonQuery();
              con.Close();
          }


          public void Insert(CargoRows row)
          {
              SqlCommand command = new SqlCommand("CargoInsert", con);
              command.Connection = con;
              command.CommandType = CommandType.StoredProcedure;
              
              
              command.Parameters.AddWithValue("@CargoName", row.CargoName);
              command.Parameters.AddWithValue("@CargoDescription", row.CargoDescription);
              command.Parameters.AddWithValue("@Mnemocode", row.Mnemocode);
              command.Parameters.AddWithValue("@IsEmpty", row.IsEmpty);
              command.Parameters.AddWithValue("@IsUsed", row.IsUsed);
              command.Parameters.AddWithValue("@ID", row.ID);
              command.Parameters.AddWithValue("@CargoCode", row.CargoCode);
              command.Parameters.AddWithValue("@ShortCode", row.CargoCode.Substring(0, row.CargoCode.Length - 1));


              con.Open();

              command.ExecuteNonQuery();
              con.Close();
          }
          public CargoRows SelectRow(int ID)
          {

              SqlCommand command = new SqlCommand("SelectRowCargo", con);
              command.Connection = con;
              command.CommandType = CommandType.StoredProcedure;
              command.Parameters.AddWithValue("@ID", ID);
              CargoRows row;


              con.Open();
              SqlDataReader data = command.ExecuteReader();
              data.Read();

              if (data.HasRows)
              {
                  row = new CargoRows()
                  {
                      ID = data.GetInt32(0),
                      IsUsed = data.GetBoolean(5),
                      IsEmpty = data.GetBoolean(6)
                  };
                  if (data.IsDBNull(1)) row.CargoName = "<Не определено>"; else row.CargoName = data.GetString(1);
                  if (data.IsDBNull(4)) row.Mnemocode = "<Не определено>"; else row.Mnemocode = data.GetString(4);
                  if (data.IsDBNull(2)) row.CargoDescription = "<Не определено>"; else row.CargoDescription = data.GetString(2);
                  if (data.IsDBNull(3)) row.CargoCode = "<Не определено>"; else row.CargoCode = data.GetString(3);
              }
              else
              {
                  row = new CargoRows();
              }
              con.Close();
              return row;
          }

          public IList<CargoRows> SelectPage(int maximumRows, int startRowIndex)
        {         
            SqlCommand command = new SqlCommand("SelectPageCargo",con);
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@PageSize", maximumRows);
            if (startRowIndex == 0) startRowIndex = 1;
            command.Parameters.AddWithValue("@Index", startRowIndex);
            con.Open();
            SqlDataReader data = command.ExecuteReader();
            IList<CargoRows> rows = new List<CargoRows>();
            while (data.Read())
            {
                CargoRows row = new CargoRows()
                      {
                          ID = data.GetInt32(1),
                          IsUsed = data.GetBoolean(11),
                          IsEmpty = data.GetBoolean(12)

                      };    
                 if (data.IsDBNull(2)) row.CargoName = "<Не определено>"; else row.CargoName = data.GetString(2);
                if (data.IsDBNull(9)) row.Mnemocode = "<Не определено>"; else row.Mnemocode = data.GetString(9);
                if (data.IsDBNull(4)) row.CargoDescription = "<Не определено>"; else row.CargoDescription = data.GetString(4);
                if (data.IsDBNull(6)) row.CargoCode = "<Не определено>"; else row.CargoCode = data.GetString(6);

                rows.Add(row);
            }
            
            con.Close();
            return rows;
          }


    public int SelectCount()
        {
            SqlCommand command = new SqlCommand("SelectCountCargo", con);
            command.Connection = con;
            command.CommandType = CommandType.StoredProcedure;
            con.Open();
            SqlDataReader data = command.ExecuteReader();
            data.Read();
            
            int i = data.GetInt32(0);
            con.Close();
            return i;
        }

}

    public class ObjectDataSource
    {
       
        private static readonly string ConnectionStringKey = "SUPSDB2";
        private string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
        public IList<InsRow> SelectAllDislocation()
        {


            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand("SelectAllRowsDislocation",con);
            command.Connection = con;
         
            command.CommandText = "SELECT * FROM tbDislocation";
            con.Open();
            SqlDataReader data =  command.ExecuteReader();
            IList <InsRow> students = new List<InsRow>();
            while(data.Read())
            {               
                students.Add(new InsRow(){ 
                    InventoryCarriageNumber=data.GetString(1),
                    WeightTons=data.GetString(2),
                    DestinationStation =data.GetString(3),
                    CargoCodeTariff =data.GetString(4),
                    СonsigneeCode =data.GetString(5),
                    ParkType =data.GetString(6),
                    StartStation=data.GetString(7),
                    OperationCode=data.GetString(8),
                    OperationDate=data.GetString(9),
                    OperationTime=data.GetString(10),
                    StationOfOperation=data.GetString(11),
                    DeliveryRoad=data.GetString(12),
                    ReceptionRoad=data.GetString(13),
                    TrainIndex=data.GetString(14),
                    TrainNumber=data.GetString(15)});
            }
            con.Close();  
            return students;
        }
    }
}

